package com.hgyc.mom.tool.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hgyc.mom.common.exception.BizException;
import com.hgyc.mom.common.util.FreeMarkerUtils;
import com.hgyc.mom.common.util.StringUtils;
import com.hgyc.mom.common.util.sql.SqlUtil;
import com.hgyc.mom.tool.entity.CustomReport;
import com.hgyc.mom.tool.entity.CustomReportField;
import com.hgyc.mom.tool.entity.CustomReportParam;
import com.hgyc.mom.tool.mapper.CustomReportMapper;
import com.hgyc.mom.tool.service.CustomReportFieldService;
import com.hgyc.mom.tool.service.CustomReportParamService;
import com.hgyc.mom.tool.service.CustomReportService;
import com.hgyc.mom.tool.vo.CustomReportParamVO;
import com.hgyc.mom.tool.vo.CustomReportVO;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 自定义报Service业务层实现
 *
 * @author fwj
 * @since 2025-04-29
 */
@Slf4j
@Service
public class CustomReportServiceImpl extends ServiceImpl<CustomReportMapper, CustomReport> implements CustomReportService {

    @Resource
    private DataSource dataSource;

    @Autowired
    private CustomReportFieldService customReportFieldService;

    @Autowired
    private CustomReportParamService customReportParamService;

    @Override
    public List<CustomReportField> parseSql(CustomReportVO customReportVO) {
        List<CustomReportField> customReportFields = new ArrayList<>();
        String sql = customReportVO.getSqlScript();

        // 1.校验sql是否为空
        if (StringUtils.isEmpty(sql)) {
            throw new BizException("sql脚本不能为空" );
        }

        // 2. 检验是否有参数，参数是否已经配置
        List<String> sqlVariables = this.getSqlVariables(sql);
        List<CustomReportParam> customReportParams = customReportVO.getCustomReportParams();
        // 提取 paramName 列表
        Set<String> paramNames = customReportParams.stream()
                .map(CustomReportParam::getParamName)
                .collect(Collectors.toSet());

        // 找出没有匹配到的变量
        List<String> unmatchedVariables = sqlVariables.stream()
                .filter(var -> !paramNames.contains(var))
                .collect(Collectors.toList());

        if (!unmatchedVariables.isEmpty()) {
            log.error("缺少参数配置：", unmatchedVariables);
            throw new BizException("sql脚本存在参数，请配置参数：" + unmatchedVariables);
        }

        String parsedSql = "";
        // 3. 获取解析之后的sql
        Map<String, Object> parseValue = this.getParseValue(customReportParams);
        try {
            parsedSql = FreeMarkerUtils.processTemplateStr(sql, parseValue);
        } catch (Exception e) {
            log.error("解析sql脚本模板失败", e);
            throw new BizException("解析sql脚本异常，请检测脚本是否正确！");
        }

        // 4. 执行sql语句
        if (StringUtils.isNotEmpty(parsedSql)) {
            customReportFields = this.getSqlResultInfo(parsedSql);
        }

        return customReportFields;
    }

    @Override
    public Page<Map<String, Object>> getReportData(Page page, CustomReportParamVO customReportParamVO) {
        String reportCode = customReportParamVO.getReportCode();
        Map<String, Object> params = customReportParamVO.getParams();

        CustomReport customReport = this.getByCode(reportCode);
        if (customReport == null) {
            throw new BizException("报表：[" + reportCode + "]" + "不存在");
        }

        List<CustomReportParam> reportParams = customReportParamService.getByReportId(customReport.getId());
        Map<String, Object> parseValue = this.getParseValue(reportParams, params);

        String parsedSql = "";
        // 1. 解析sql
        try {
            parsedSql = FreeMarkerUtils.processTemplateStr(customReport.getSqlScript(), parseValue);
        } catch (Exception e) {
            log.error("解析sql脚本模板失败", e);
            throw new BizException("解析sql脚本异常，请检测脚本是否正确！");
        }

        // 2. 添加查询条件
        List<CustomReportField> reportFields = customReportFieldService.getByReportId(customReport.getId());
        String conditionSql = this.buildConditionSql(parsedSql, reportFields, params);

        // 3. 添加分页
        String countSql = SqlUtil.getCountSql(conditionSql);
        String pageSql = SqlUtil.getPageSql(conditionSql, page, dataSource);

        long totalCount = this.getTotalCount(countSql);
        List<Map<String, Object>> records = this.executeSql(pageSql);
        page.setTotal(totalCount);
        page.setRecords(records);

        return page;
    }

    /**
     * 提取sql中的变量
     * @param sql sql脚本
     * @return 变量
     */
    private List<String> getSqlVariables(String sql) {
        // 匹配 ${xxx} 格式，并捕获 xxx 部分
        Pattern pattern = Pattern.compile("\\$\\{([^}]+)}");
        Matcher matcher = pattern.matcher(sql);

        List<String> variables = new ArrayList<>();

        while (matcher.find()) {
            String variable = matcher.group(1); // 提取括号中捕获的内容（即 xxx）
            variables.add(variable);
            System.out.println("提取的变量名: " + variable);
        }

        return variables;
    }

    /**
     * 获取模板解析值
     * @param customReportParams 参数信息
     * @return 解析值
     */
    private Map<String, Object> getParseValue(List<CustomReportParam> customReportParams) {
        Map<String, Object> parseValue = new HashMap<>();
        for (CustomReportParam customReportParam : customReportParams) {
            if (StringUtils.isNotEmpty(customReportParam.getDefaultValue())) {
                parseValue.put(customReportParam.getParamName(), customReportParam.getDefaultValue());
            } else {
                parseValue.put(customReportParam.getParamName(), "1");
            }

        }
        return parseValue;
    }

    /**
     * 获取解析模板值
     * 合并前端传入参数
     * @param customReportParams 报表参数
     * @param params 传入参数
     * @return 解释值
     */
    private Map<String, Object> getParseValue(List<CustomReportParam> customReportParams, Map<String, Object> params) {
        Map<String, Object> parseValue = new HashMap<>();

        String errorMsg = "";

        for (CustomReportParam customReportParam : customReportParams) {
            if (params.containsKey(customReportParam.getParamName())) {
                parseValue.put(customReportParam.getParamName(), params.get(customReportParam.getParamName()));
            } else if (customReportParam.getDefaultValue() != null) {
                parseValue.put(customReportParam.getParamName(), customReportParam.getDefaultValue());
            } else {
                errorMsg = errorMsg + "参数：" + customReportParam.getParamName() + "不能为空；";
            }
        }

        if (StringUtils.isNotEmpty(errorMsg)) {
            throw new BizException(errorMsg);
        }

        return parseValue;
    }

    /**
     * 获取sql执行结果字段信息
     * @param sql
     * @return
     */
    private List<CustomReportField> getSqlResultInfo(String sql) throws BizException {
        if (dataSource instanceof HikariDataSource hikariDataSource) {
            List<CustomReportField> reportFields = new ArrayList<>();
            try (Connection connection = hikariDataSource.getConnection();
                 Statement statement = connection.createStatement()) {
                if (sql.trim().toLowerCase().startsWith("select")) {
                    log.info("执行sql：{}", sql);
                    ResultSet resultSet = statement.executeQuery(sql);

                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    for (int i = 1; i <= columnCount; i++) {
                        CustomReportField customReportField = new CustomReportField();
                        customReportField.setId(IdWorker.getId());
                        customReportField.setFieldName(metaData.getColumnLabel(i));
                        customReportField.setFieldType(metaData.getColumnTypeName(i));
                        reportFields.add(customReportField);
                    }
                    return reportFields;
                } else {
                    throw new BizException("只能执行select语句");
                }
            }catch (SQLException e) {
                log.error("SQL 执行失败: ", e.getMessage());
                throw new BizException("SQL 执行失败");
            }catch (BizException e) {
                throw new BizException(e.getMessage());
            }
        }
        return null;
    }

    /**
     * 执行sql
     * @param sql
     * @return
     */
    private List<Map<String, Object>> executeSql(String sql) throws BizException {
        if (dataSource instanceof HikariDataSource hikariDataSource) {
            List<Map<String, Object>> resultList = new ArrayList<>();
            try (Connection connection = hikariDataSource.getConnection();
                 Statement statement = connection.createStatement()) {
                if (sql.trim().toLowerCase().startsWith("select")) {
                    log.info("执行sql：{}", sql);
                    ResultSet resultSet = statement.executeQuery(sql);

                    ResultSetMetaData metaData = resultSet.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    while (resultSet.next()) {
                        Map<String, Object> rowMap = new HashMap<>();
                        for (int i = 1; i <= columnCount; i++) {
                            String columnName = metaData.getColumnName(i);
                            Object value = resultSet.getObject(i);
                            rowMap.put(columnName, value);
                        }
                        resultList.add(rowMap);
                    }
                    return resultList;
                } else {
                    throw new BizException("只能执行select语句");
                }
            }catch (SQLException e) {
                log.error("SQL 执行失败: ", e.getMessage());
                throw new BizException("SQL 执行失败");
            }catch (BizException e) {
                throw new BizException(e.getMessage());
            }
        }
        return null;
    }

    /**
     * 获取sql记录数
     * @param countSql sql
     * @return 记录数
     */
    public long getTotalCount(String countSql) throws BizException {
        try (Connection connection = dataSource.getConnection()) {
            // 根据数据库类型判断
            try (PreparedStatement preparedStatement = connection.prepareStatement(countSql)) {
                ResultSet rs = preparedStatement.executeQuery();
                if (rs.next()) {
                    return rs.getLong(1); // 获取总条数
                }
            }

        } catch (SQLException e) {
            throw new BizException("数据库执行Sql错误");
        }
        return 0L;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public CustomReportVO saveReport(CustomReportVO customReportVO) {
        //如果是新增校验编码是否已存在
        if (customReportVO.getId() == null) {
            CustomReport existReport = this.getByCode(customReportVO.getReportCode());
            if (existReport != null) {
                throw new BizException("编码："+customReportVO.getReportCode()+"已存在");
            }
        }

        //保存表头
        this.saveOrUpdate(customReportVO);

        //保存字段
        List<CustomReportField> customReportFields = customReportVO.getCustomReportFields();
        if (customReportVO.getId() != null) {
            customReportFieldService.deleteByReportId(customReportVO.getId());
        }
        if (!customReportFields.isEmpty()) {
            customReportFields.forEach(field -> {
                field.setId(null);
                field.setReportId(customReportVO.getId());
            });
            customReportFieldService.saveBatch(customReportFields);
        }

        //保存参数
        List<CustomReportParam> customReportParams = customReportVO.getCustomReportParams();
        if (customReportVO.getId() != null) {
            customReportParamService.deleteByReportId(customReportVO.getId());
        }
        if (!customReportParams.isEmpty()) {
            customReportParams.forEach(param -> {
                param.setId(null);
                param.setReportId(customReportVO.getId());
            });
            customReportParamService.saveBatch(customReportParams);
        }

        return customReportVO;
    }

    private CustomReport getByCode(String code) {
        LambdaQueryWrapper<CustomReport> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(CustomReport::getReportCode, code);
        return getOne(queryWrapper, false);
    }

    @Override
    public CustomReportVO getReportInfo(Long id) {
        CustomReportVO customReportVO = new CustomReportVO();

        CustomReport customReport = this.getById(id);
        BeanUtils.copyProperties(customReport, customReportVO);

        List<CustomReportField> fields = customReportFieldService.getByReportId(id);
        customReportVO.setCustomReportFields(fields);

        List<CustomReportParam> params = customReportParamService.getByReportId(id);
        customReportVO.setCustomReportParams(params);

        return customReportVO;
    }

    @Override
    public List<CustomReportField> getFieldByCode(String code) {
        CustomReport report = this.getByCode(code);
        if (report == null) {
            throw new BizException("获取报表：" + code + "失败！");
        }

        return customReportFieldService.getByReportId(report.getId());
    }

    /**
     * 创建带查询条件的sql
     * @param baseSql 原sql
     * @param fields 查询字段
     * @param params 查询值
     * @return 封装后的sql
     */
    private String buildConditionSql(String baseSql, List<CustomReportField> fields, Map<String, Object> params) {
        StringBuilder sqlBuilder = new StringBuilder();
        List<String> conditions = new ArrayList<>();

        for (CustomReportField field : fields) {
            if (field.getIsQuery() ==null || field.getIsQuery() != 1) continue;

            String fieldName = field.getFieldName();
            String fieldType = field.getFieldType().toUpperCase();
            Object value = params.get(fieldName);

            if (value == null || (value instanceof String && ((String) value).trim().isEmpty())) {
                continue;
            }

            if (fieldType.contains("CHAR") || fieldType.contains("TEXT")) {
                conditions.add("t." + fieldName + " LIKE '%" + value + "%'");
            } else if (fieldType.contains("INT") || fieldType.contains("DOUBLE") || fieldType.contains("DECIMAL")) {
                conditions.add("t." + fieldName + " = " + value);
            } else if (fieldType.contains("DATE") || fieldType.contains("TIME")) {
                if (value instanceof Map) {
                    Map<?, ?> range = (Map<?, ?>) value;
                    Object start = range.get("start");
                    Object end = range.get("end");
                    if (start != null && end != null) {
                        conditions.add("t." + fieldName + " BETWEEN '" + start + "' AND '" + end + "'");
                    } else if (start != null) {
                        conditions.add("t." + fieldName + " >= '" + start + "'");
                    } else if (end != null) {
                        conditions.add("t." + fieldName + " <= '" + end + "'");
                    }
                } else {
                    conditions.add("t." + fieldName + " = '" + value + "'");
                }
            } else {
                conditions.add("t." + fieldName + " = '" + value + "'");
            }
        }

        sqlBuilder.append("SELECT * FROM (\n").append(baseSql.trim()).append("\n) t");

        if (!conditions.isEmpty()) {
            sqlBuilder.append("\nWHERE ").append(String.join(" AND ", conditions));
        }

        return sqlBuilder.toString();
    }

}
